package com.cap.bts.crmdp.dealer.remind.dao.impl;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;

import javax.inject.Inject;

import org.apache.commons.lang3.StringUtils;
import org.apache.log4j.Logger;
import org.hibernate.Session;
import org.hibernate.engine.SessionFactoryImplementor;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Repository;

import com.cap.bts.crmdp.dealer.remind.dao.IServiceRemindDao;
import com.cap.bts.crmdp.dealer.remind.domain.ServiceRemind;
import com.cap.bts.crmdp.dealer.remind.dto.ServiceRemindInterimDto;
import com.cap.bts.framework.common.dao.callback.NativeQueryBySqlJpaCallback;
import com.cap.bts.framework.common.dao.impl.BaseDaoImpl;
import com.cap.bts.framework.common.exception.AppException;

@Repository("serviceRemindDao")
public class ServiceRemindDaoImpl extends BaseDaoImpl<ServiceRemind, Long> implements IServiceRemindDao {
	@Inject
	public ServiceRemindDaoImpl(@Value("com.cap.bts.crmdp.dealer.remind.domain.ServiceRemind") Class<ServiceRemind> clazz) {
		super(clazz);
	}
	
	private static final Logger LOGGER = Logger.getLogger(ServiceRemindDaoImpl.class);

	@Override
	public List<ServiceRemind> getServiceRemindByCarPlateAndMaintenance(String carPlate, String maintenance)
			throws AppException {
		String sql = "select * from tm_raw_service_remind where car_plate = '" + carPlate + "'"
				+ " and maintenance_item = '" + maintenance + "'";
		return this.getListByNativeQuery(sql);
	}

	@Override
	public List<String> getAllServiceRemindWithTyre() throws AppException {
		String sql = "select b.car_plate from tm_raw_service_remind b WHERE"
				+ " b.maintenance_item IN ( 'TyreFront_MI', 'TyreRear_MI' )"
				+ " GROUP BY b.car_plate HAVING COUNT(b.car_plate) > 1";
		return this.getJpaTemplate().executeFind(new NativeQueryBySqlJpaCallback<String>(sql));
	}

	@Override
	public List<ServiceRemind> getSRWithTyreByCarplate(String carplate) throws AppException {
		String sql = "select * from tm_raw_service_remind where"
				+ " car_plate ='" + carplate +"'"
						+ " and maintenance_item IN ( 'TyreFront_MI', 'TyreRear_MI' )" ;
		return this.getListByNativeQuery(sql);
	}

	@Override
	public List<ServiceRemind> getSRWithBrakeByCarplate(String carplate) throws AppException {
		String sql = "select * from tm_raw_service_remind where"
				+ " car_plate ='" + carplate +"'"
						+ " and maintenance_item IN ( 'BrakePadFront_MI', 'BrakePadRear_MI' )" ;
		return this.getListByNativeQuery(sql);
	}

	@Override
	public List<String> getAllServiceRemindWithBrake() throws AppException {
		String sql = "select b.car_plate from tm_raw_service_remind b WHERE"
				+ " b.maintenance_item IN ( 'BrakePadFront_MI', 'BrakePadRear_MI' )"
				+ " GROUP BY b.car_plate HAVING COUNT(b.car_plate) > 1";
		return this.getJpaTemplate().executeFind(new NativeQueryBySqlJpaCallback<String>(sql));
	}

	@Override
	public List<String> getAllServiceRemindWithBrakeDisc() throws AppException {
		String sql = "select b.car_plate from tm_raw_service_remind b WHERE"
				+ " b.maintenance_item IN ( 'BrakeDiscFront_MI', 'BrakeDiscRear_MI' )"
				+ " GROUP BY b.car_plate HAVING COUNT(b.car_plate) > 1";
		return this.getJpaTemplate().executeFind(new NativeQueryBySqlJpaCallback<String>(sql));
	}

	@Override
	public List<ServiceRemind> getSRWithBrakeDiscByCarplate(String carplate) throws AppException {
		String sql = "select * from tm_raw_service_remind where"
				+ " car_plate ='" + carplate +"'"
						+ " and maintenance_item IN ( 'BrakeDiscFront_MI', 'BrakeDiscRear_MI' )" ;
		return this.getListByNativeQuery(sql);
	}

	@Override
	public void insertAllServiceRemind(List<ServiceRemindInterimDto> list) throws AppException {
		Session session = (org.hibernate.Session) getEntityManager().getDelegate();
		SessionFactoryImplementor sf = (SessionFactoryImplementor) session.getSessionFactory();
		Connection connection = null;
		PreparedStatement pstmt = null;
		PreparedStatement pstmt2 = null;
		int i = 0;
		try {
			LOGGER.info("start insert ServiceRemind");
			connection = sf.getConnectionProvider().getConnection();
			connection.setAutoCommit(false);
			String batchId = null;
			pstmt = connection.prepareStatement("INSERT INTO tm_raw_service_remind_copy  ( " +
			        "    car_plate , " +
			        "    car_model , " +
			        "    maintenance_item , " +
			        "    last_part_brand , " +
			        "    last_part_name , " +
			        "    last_maintenance_store , " +
			        "    last_maintenance_date , " +
			        "    last_maintenance_mileage , " +
			        "    last_carcheck_store , " +
			        "    last_carcheck_date , " +
			        "    last_carcheck_mileage , " +
			        "    next_calc_basis , " +
			        "    maintenance_spec_basis , " +
			        "    next_calc_date," +
			        "    last_calc_date," +
			        "    batch_id," +
			        "    next_calc_mileage," +
			        "    data_requirement," +
			        "    use_standard_average_KM," +
			        "    create_by," +
			        "    create_date," +
			        "    last_update_by," +
			        "    last_update_date" +
			        ") " +
			        "VALUES " +
			        "   ( " +
			        "?, ?, ?, ?, ?," + 
			        "?, ?, ?, ?, ?," +
			        "?, ?, ?, ?, ?," +
			        "?, ?, ?, ?, ?," +
			        "?, ?, ?" +
			        "   ) ");
			for(ServiceRemindInterimDto serviceRemindInterimDto:list){
				batchId = serviceRemindInterimDto.getBatchId();
				i++;
				pstmt.setString(1, serviceRemindInterimDto.getCarPlate());
				pstmt.setString(2, serviceRemindInterimDto.getCarModel());
				pstmt.setString(3, serviceRemindInterimDto.getMaintenanceItem());
				pstmt.setString(4, serviceRemindInterimDto.getLastPartBrand());
				pstmt.setString(5, serviceRemindInterimDto.getLastPartName());
				pstmt.setString(6, serviceRemindInterimDto.getLastMaintenanceStore());
				if(serviceRemindInterimDto.getLastMaintenanceDate() != null){
					pstmt.setDate(7, new Date(serviceRemindInterimDto.getLastMaintenanceDate().getTime()));
				}else{
					pstmt.setDate(7, null);
				}
				pstmt.setDouble(8, serviceRemindInterimDto.getLastMaintenanceMileage());
				pstmt.setString(9, serviceRemindInterimDto.getLastCarcheckStore());
				if(serviceRemindInterimDto.getLastCarcheckDate() != null){
					pstmt.setDate(10, new Date(serviceRemindInterimDto.getLastCarcheckDate().getTime()));
				}else{
					pstmt.setDate(10, null);
				}
				pstmt.setDouble(11, serviceRemindInterimDto.getLastCarcheckMileage());
				pstmt.setString(12, serviceRemindInterimDto.getNextCalcbasis());
				pstmt.setString(13, serviceRemindInterimDto.getMaintenanceSpecBasis());
				if(serviceRemindInterimDto.getNextCalcDate() != null){
					pstmt.setDate(14, new Date(serviceRemindInterimDto.getNextCalcDate().getTime()));
				}else{
					pstmt.setDate(14, null);
				}
				if(serviceRemindInterimDto.getLastCalcDate() != null){
					pstmt.setDate(15, new Date(serviceRemindInterimDto.getLastCalcDate().getTime()));
				}else{
					pstmt.setDate(15, null);
				}
				pstmt.setString(16, serviceRemindInterimDto.getBatchId());
				pstmt.setDouble(17, serviceRemindInterimDto.getNextCalcMileage());
				pstmt.setString(18, serviceRemindInterimDto.getDataRequirement());
				pstmt.setString(19, serviceRemindInterimDto.getUseStandardAverageKM());
				pstmt.setLong(20, 0L);
				pstmt.setDate(21, new Date(new java.util.Date().getTime()));
				pstmt.setLong(22, 0L);
				pstmt.setDate(23, new Date(new java.util.Date().getTime()));
				pstmt.addBatch();
//				LOGGER.info(i);
				if(i%500 == 0){
					LOGGER.info("start commit");
					pstmt.executeBatch();     
					connection.commit();     
					LOGGER.info("end commit");
					pstmt.clearBatch(); 
				}
			}
			if(i%500 != 0){
				LOGGER.info("start commit");
				pstmt.executeBatch();     
				connection.commit();  
				LOGGER.info("end commit");
			}
			
			if(StringUtils.isNotBlank(batchId)){
				String sql1 = "insert into  tm_raw_service_remind_arch (`id`, `car_plate`, `car_model`, `maintenance_item`, `last_part_brand`, `last_part_name`, `last_maintenance_store`, `last_maintenance_date`, `last_maintenance_mileage`, `last_carcheck_store`, `last_carcheck_date`, `last_carcheck_mileage`, `next_calc_basis`, `maintenance_spec_basis`, `next_calc_date`, `next_calc_mileage`, `use_standard_average_KM`, `create_by`, `create_date`, `last_update_by`, `last_update_date`)\n" +
						"select `id`, `car_plate`, `car_model`, `maintenance_item`, `last_part_brand`, `last_part_name`, `last_maintenance_store`, `last_maintenance_date`, `last_maintenance_mileage`, `last_carcheck_store`, `last_carcheck_date`, `last_carcheck_mileage`, `next_calc_basis`, `maintenance_spec_basis`, `next_calc_date`, `next_calc_mileage`, `use_standard_average_KM`, `create_by`, `create_date`, `last_update_by`, `last_update_date` from tm_raw_service_remind a where \n" +
						"EXISTS (select b.data_requirement from tm_raw_service_remind_copy b where batch_id = '" + batchId + "' and a.data_requirement = b.data_requirement )";
				;
				String sql2 =  "DELETE from tm_raw_service_remind \n" +
						" where \n" +
						" EXISTS (select 1 from tm_raw_service_remind_copy b where b.batch_id = '" + batchId + "' and b.data_requirement = tm_raw_service_remind.data_requirement )";
				
				String sql3 = "insert into  tm_raw_service_remind (`data_requirement`, `car_plate`, `car_model`, `maintenance_item`, `last_part_brand`, `last_part_name`, `last_maintenance_store`, `last_maintenance_date`, `last_maintenance_mileage`, `last_carcheck_store`, `last_carcheck_date`, `last_carcheck_mileage`, `next_calc_basis`, `maintenance_spec_basis`, `next_calc_date`, `next_calc_mileage`, `use_standard_average_KM`, `create_by`, `create_date`, `last_update_by`, `last_update_date`)\n" +
						" select `data_requirement`, `car_plate`, `car_model`, `maintenance_item`, `last_part_brand`, `last_part_name`, `last_maintenance_store`, `last_maintenance_date`, `last_maintenance_mileage`, `last_carcheck_store`, `last_carcheck_date`, `last_carcheck_mileage`, `next_calc_basis`, `maintenance_spec_basis`, `next_calc_date`, `next_calc_mileage`, `use_standard_average_KM`, `create_by`, `create_date`, `last_update_by`, `last_update_date` from tm_raw_service_remind_copy a where \n" +
						" batch_id = '" + batchId + "'";
				
				String sql4 =  " delete from tm_raw_service_remind_copy where batch_id = '" + batchId + "'";
				
				
				pstmt2 = connection.prepareStatement(sql1);
				
				pstmt2.addBatch(sql2);
				pstmt2.addBatch(sql3);
				pstmt2.addBatch(sql4);
				
				pstmt2.executeBatch();
				
			}
			connection.commit();
			
			
		} catch (Exception e) {
			throw new AppException("ERROR", e);
		}finally{
			try {
				connection.setAutoCommit(true);
				pstmt.close();
				connection.close();
				LOGGER.info("end insert serviceRemindInterim");
			} catch (SQLException e) {
				pstmt = null;
				connection = null;
			}
		}
		
	}

	@Override
	public void deleteServiceRemindInterim() throws AppException {
		
		Session session = (org.hibernate.Session) getEntityManager().getDelegate();
		SessionFactoryImplementor sf = (SessionFactoryImplementor) session.getSessionFactory();
		Connection connection = null;
		PreparedStatement pstmt = null;
		
		try {
			connection = sf.getConnectionProvider().getConnection();
			connection.setAutoCommit(false);
			pstmt = connection.prepareStatement("delete from tm_raw_service_remind_copy");
			
			LOGGER.info("start commit");
			pstmt.executeBatch();
			connection.commit();
			LOGGER.info("end commit");
			pstmt.clearBatch();
		} catch (Exception e) {
			throw new AppException("ERROR", e);
		}finally{
			try {
				connection.setAutoCommit(true);
				pstmt.close();
				connection.close();
				LOGGER.info("end delete tm_raw_service_remind_copy");
			} catch (SQLException e) {
				pstmt = null;
				connection = null;
			}
		}
	}

	@Override
	public List<ServiceRemind> getAllServiceRemind() throws AppException {
		String sql = "select * from tm_raw_service_remind WHERE car_model != '' GROUP BY car_model";
		return this.getListByNativeQuery(sql);
	}

}
